package com.nextbus.database;

import java.io.IOException;
import java.io.InputStream;
import java.io.StringReader;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParserFactory;

import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;

import android.R.integer;
import android.app.ProgressDialog;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.graphics.drawable.Drawable;
import android.os.StatFs;
import android.util.Log;

import com.google.android.maps.GeoPoint;
import com.nextbus.main.R;
import com.nextbus.main.UpdateAsyncTask;
import com.nextbus.model.Direction;
import com.nextbus.model.DirectionsAndStops;
import com.nextbus.model.Prediction;
import com.nextbus.model.Route;
import com.nextbus.model.RouteConfig;
import com.nextbus.model.Stop;
import com.nextbus.parser.BusLocationParser;
import com.nextbus.ui.BusOverlay;
import com.nextbus.ui.BusOverlayItem;
import com.nextbus.util.Constants;
import com.nextbus.util.HttpDownXMl;
import com.nextbus.util.HttpDownloader;
import com.nextbus.util.ProgressMessage;

public class DatabaseHelper {
    private final static String TAG = "NextBusRouteData";

    final static String DATABASE_NAME = "NextBus.db";

    final static int DATABASE_VERSION = 3;

    // table name
    private static final String TABLE_ROUTES = "routes";

    private static final String TABLE_STOPS = "stops";

    private static final String TABLE_STOPMAPPING = "stopmapping";

    private static final String TABLE_DIRECTIONMAPPING = "directionmapping";

    private static final String TABLE_DIRECTIONS = "directions";

    //
    private static final String[] COLUMN = new String[] { "route" };

    /**
     * 
     * @author xxq
     * 
     */
    private class DBHelper extends SQLiteOpenHelper {
        public DBHelper(Context context, String dbName, int version) {
            super(context, dbName, null, version);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            Log.d(TAG, "onCreate");
            db.execSQL("CREATE TABLE IF NOT EXISTS directions (dirTag STRING PRIMARY KEY, dirNameKey STRING, dirTitleKey STRING, dirRouteKey STRING)");
            db.execSQL("CREATE TABLE IF NOT EXISTS routes (route STRING PRIMARY KEY, color INTEGER, oppositecolor INTEGER, pathblob BLOB)");
            db.execSQL("CREATE TABLE IF NOT EXISTS stops (tag STRING PRIMARY KEY, lat FLOAT, lon FLOAT, title STRING)");
            db.execSQL("CREATE TABLE IF NOT EXISTS stopmapping (route STRING, tag STRING, dirTag STRING, PRIMARY KEY (route, tag))");
            db.execSQL("CREATE TABLE IF NOT EXISTS directionmapping (direction STRING, stop STRING, PRIMARY KEY(direction, stop))");
            // db.execSQL("CREATE TABLE IF NOT EXISTS IDX_directionmapping ON directionmapping (direction)");
            db.execSQL("CREATE INDEX IF NOT EXISTS IDX_routemapping ON stopmapping (route)");
            db.execSQL("CREATE INDEX IF NOT EXISTS IDX_stopmapping ON stopmapping (tag)");
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
            Log.d(TAG, "onUpdate");
            Log.d(TAG, "update from version " + arg1 + " to " + arg2);
            db.execSQL("DROP TABLE IF EXISTS directions");
            db.execSQL("DROP TABLE IF EXISTS routes");
            db.execSQL("DROP TABLE IF EXISTS stops");
            db.execSQL("DROP TABLE IF EXISTS stopmapping");
            db.execSQL("DROP TABLE IF EXISTS directionmapping");
            onCreate(db);
        }

        // 查询所有路线，得出allroute列表
        public Cursor query() {
            SQLiteDatabase db = getWritableDatabase();
            Cursor c = db.query(TABLE_ROUTES, COLUMN, null, null, null, null,
                    null);
            return c;
        }

        // 查询某一个表中带有一个where条件
        public Cursor query(String querySQL, String[] selection) {
            SQLiteDatabase db = getWritableDatabase();
            Cursor c = db.rawQuery(querySQL, selection);
            return c;
        }
    }

    private final DBHelper dbHelper;

    /**
     * insert的地方比较多，用到values也就比较多，所以专门搞一个通用下
     */
    private ContentValues values = null;

    private ProgressDialog progressDialog;

    public DatabaseHelper(Context context) {
        dbHelper = new DBHelper(context, DATABASE_NAME, DATABASE_VERSION);
    }

    public boolean isRouteConfigDataInit() {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        Cursor cursor = db.rawQuery("select count(*) as count from routes",
                null);
        int size = -1;
        if (cursor.moveToNext()) {
            size = cursor.getInt(0);
        }

        cursor.close();
        // Log.d(TAG, "error may occur @ isRouteConfigDataInit size = " + size);
        if (size == Constants.ROUTE_SIZE) {
            return true;
        }
        return false;
    }

    public boolean checkFreeSpace() {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        try {
            String path = db.getPath();
            Log.d(TAG, "path @ checkFreeSpace " + path);
            StatFs statFs = new StatFs(path);
            Log.d(TAG, "statFs @ checkFreeSpace " + statFs);
            long freeSpace = (long) statFs.getAvailableBlocks()
                    * (long) statFs.getBlockSize();
            Log.d(TAG, "freeSpace @ checkFreeSpace " + freeSpace);
            return freeSpace >= 1024 * 1024 * 4;
        } catch (Exception e) {
            // if for some reason we don't have permission to check free space
            // available, just hope that everything's ok
            return true;
        } finally {
            db.close();
        }
    }

    public boolean writeAllRouteConfigDataToDB(
            ArrayList<RouteConfig> routeConfigs, UpdateAsyncTask publisher) {
        publisher.publish(new ProgressMessage(
                ProgressMessage.PROGRESS_DIALOG_ON, "Write To DB", null));
        publisher.publish(new ProgressMessage(ProgressMessage.SET_MAX,
                routeConfigs.size()));

        SQLiteDatabase db = dbHelper.getWritableDatabase();
        Iterator iter = routeConfigs.iterator();
        int counter = 0;
        while (iter.hasNext()) {
            db.beginTransaction();
            try {
                RouteConfig routeConfig = (RouteConfig) iter.next();
                writeRouteDataToDB(db, routeConfig.getRoute());
                writeStopsAndStopmappingToDB(db,
                        routeConfig.getRouteTagKeyToStopTagvalue());
                writeStopsDataToDB(db, routeConfig.getStops());
                writeDirectionAndDirectionMappingDataToDB(db,
                        routeConfig.getDirectionsToStopsMapping());

                db.setTransactionSuccessful();
            } catch (Exception e) {
                e.printStackTrace();
                return false;
            } finally {
                db.endTransaction();
            }
            publisher.publish(++counter);
        }
        db.close();
        publisher.publish(new ProgressMessage(
                ProgressMessage.PROGRESS_DIALOG_OFF));
        return true;
    }

    private void writeDirectionAndDirectionMappingDataToDB(SQLiteDatabase db,
            ArrayList<DirectionsAndStops> directionsToStopsMapping) {
        Iterator iterDirections = directionsToStopsMapping.iterator();
        while (iterDirections.hasNext()) {
            DirectionsAndStops directionToStopsMapping = (DirectionsAndStops) iterDirections
                    .next();
            Direction direction = directionToStopsMapping.getDirection();
            initValues(values);
            values.put("dirTag", direction.getDirTag());
            values.put("dirNameKey", direction.getDirNameKey());
            values.put("dirTitlekey", direction.getDirTitlekey());
            values.put("dirRouteKey", direction.getDirRouteKey());
            long id = db.insert("directions", null, values);
            releaseValues(values);
            if (id == -1) {
                Log.d(TAG, "error may occur @ writeDirectionsDataToDB");
            }
            String directionTag = direction.getDirTag();
            ArrayList<String> stopsMapping = directionToStopsMapping
                    .getStopsMapping();
            Iterator stops_mapping = stopsMapping.iterator();
            while (stops_mapping.hasNext()) {
                initValues(values);
                String stopTag = (String) stops_mapping.next();
                values.put("direction", directionTag);
                values.put("stop", stopTag);
                id = db.insert("directionMapping", null, values);
                releaseValues(values);
                if (id == -1) {
                    Log.d(TAG, "error may occur @ writeDirectionsDataToDB");
                }
            }
        }

    }



    private void writeStopsDataToDB(SQLiteDatabase db, ArrayList<Stop> stops) {
        Iterator iter = stops.iterator();
        while (iter.hasNext()) {
            Stop stop = (Stop) iter.next();
            initValues(values);
            values.put("tag", stop.getTag());
            values.put("lat", Float.parseFloat(stop.getLat()));
            values.put("lon", Float.parseFloat(stop.getLon()));
            values.put("title", stop.getTitle());
            long id = db.insert("stops", null, values);
            releaseValues(values);
            if (id == -1) {
                Log.d(TAG, "error may occur @ writeStopsDataToDB");
            }
        }

    }

    private void writeStopsAndStopmappingToDB(SQLiteDatabase db,
            HashMap<String, String> routeTagKeyToStopTagvalue) {
        Iterator keyIter = routeTagKeyToStopTagvalue.keySet().iterator();
        while (keyIter.hasNext()) {
            initValues(values);
            String key = (String) keyIter.next();
            String value = routeTagKeyToStopTagvalue.get(key);
            values.put("route", value);
            values.put("tag", key);
            long id = db.insert("stopmapping", null, values);
            releaseValues(values);
            if (id == -1) {
                Log.d(TAG, "error may occur @ writeStopsAndStopmappingToDB");
            }
        }

    }

    private void writeRouteDataToDB(SQLiteDatabase db, Route route) {
        initValues(values);
        values.put("route", route.getRouteTag());
        long id = db.insert("routes", null, values);
        releaseValues(values);
        if (id == -1) {
            Log.d(TAG, "error may occur @ writeRouteDataToDB");
        }
    }

    // ???? 有待优化，可以直接进行跨表查询
    public ArrayList<String> getStopNameListByRoute(String route) {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Cursor cursor = db.query(TABLE_STOPMAPPING, null, "route = " + route,
                null, null, null, null);
        ArrayList<String> stopNameList = new ArrayList<String>();
        for (cursor.moveToFirst(); !(cursor.isAfterLast()); cursor.moveToNext()) {
            stopNameList.add(cursor.getString(1));
        }
        close(cursor, null, db);
        return stopNameList;
    }
    
 // 从数据库得到一条路线对应的n个方向
    public List<String> getDirectionsNameList(String routeName) {
    	System.out.println("DatabaseHelper.getDirectionsNameList()");
    	
    	SQLiteDatabase db = dbHelper.getReadableDatabase();
        ArrayList<String>directionList = new ArrayList<String>();
        String sql="select * from directions where dirRouteKey=?";
        String args[]={routeName};
        Cursor cursor = db.rawQuery(sql, args);
        if (cursor.moveToFirst()) {
            for (int i = 0, num = cursor.getCount(); i < num; i++) {
            	cursor.moveToPosition(i);
                directionList.add(cursor.getString(cursor.getColumnIndex("dirTag")));
            }
        }
        close(cursor, null, db);
        //direction.close();
        return directionList;
    }
    
    public ArrayList<Stop> getStopListByStopNameList(String route) {
        ArrayList<String> stopNameList = getStopNameListByRoute(route);
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        ArrayList<Stop> stops = new ArrayList<Stop>();
        Cursor cursor = null;
        for (String stopName : stopNameList) {
            Log.d(TAG, "stopName = " + stopName);
           cursor = db.query(TABLE_STOPS, null, "tag='" + stopName + "'", null, null, null, null);
           if (cursor.moveToNext()) {
               String tag = cursor.getString(0);
               Float lat = cursor.getFloat(1);
               Float lon = cursor.getFloat(2);
               String title = cursor.getString(3);
               Stop stop = new Stop(tag, lat.toString(), lon.toString(), title);
               stops.add(stop);
           }
           cursor.close();
        }
        close(cursor, null, db);
        return stops;
    }
   
    public RouteConfig getRouteConfig(String route,String direction,UpdateAsyncTask publisher){
    	//一次性下载一条路线所有站点的预测信息的url
    	StringBuilder  strmurl=new StringBuilder(Constants.strmurl);
    	
    	RouteConfig routeConfig=new RouteConfig();
        
        ArrayList<String>stoptagList = new ArrayList<String>();
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        String sql="select * from directionmapping where direction=?";
        String []args={direction};
        //由方向的tag获得方向上的站点的tag
        Cursor cursor = db.rawQuery(sql, args);
        System.out.println("tag----->" + cursor.getCount());
        if (cursor.moveToFirst()) {
            for (int i = 0, num = cursor.getCount(); i < num; i++) {
                cursor.moveToPosition(i);
                String tag = cursor.getString(cursor.getColumnIndex("stop"));
                stoptagList.add(tag);
                //获得多站点下载预测信息的url
                strmurl.append("&stops=").append(route).append("|").append(tag);
            }
        }
        cursor.close();
        System.out.println(strmurl);
        //下载数据
        
        HttpDownloader httpdownloader=new HttpDownloader();
        String preResultString =null;
        try
        {
        preResultString=httpdownloader.download(strmurl.toString());
        }
        catch (Exception e) {
			// TODO: handle exception
        	publisher.publish(new ProgressMessage(ProgressMessage.TOAST,"1","请检查你的网络状态,目前下载不到信息"));
		}
        BusLocationParser parser = null;
        if(preResultString!=null)
        {
        	//如果没有下载到数据则不进行解析
        	System.out.println("下载到数据");
        	System.out.println(preResultString);
        	//解析下载到的数据
        	SAXParserFactory factory = SAXParserFactory.newInstance();
        	try {
        		XMLReader reader = factory.newSAXParser().getXMLReader();
			    parser = new BusLocationParser(direction);
				reader.setContentHandler(parser);
				reader.parse(new InputSource(new StringReader(preResultString)));
			
        	} catch (SAXException e) {
			// TODO Auto-generated catch block
        		e.printStackTrace();
        	} catch (ParserConfigurationException e) {
			// TODO Auto-generated catch block
        		e.printStackTrace();
        	} catch (IOException e) {
			// TODO Auto-generated catch block
        		e.printStackTrace();
        	}
       }
        //由站点的tag的到站点的经纬度信息
        ArrayList<Stop>stopList = new ArrayList<Stop>();    
        GeoPoint point = null;
        for (int i = 0, num = stoptagList.size(); i < num; i++) {
        	String sqlforpoint="select * from stops where tag=?";
        	String[]args1={stoptagList.get(i)};
        	Cursor cstopforpoint=db.rawQuery(sqlforpoint,args1);
            if (cstopforpoint.moveToFirst()) {
                cstopforpoint.moveToPosition(0);
                String tag = cstopforpoint.getString(cstopforpoint.getColumnIndex("tag"));
                String lat = String.valueOf(cstopforpoint.getFloat(cstopforpoint.getColumnIndex("lat")));
                String lon = String.valueOf(cstopforpoint.getFloat(cstopforpoint.getColumnIndex("lon")));
                String title = cstopforpoint.getString(cstopforpoint.getColumnIndex("title"));
                Stop stop = new Stop(tag,lat, lon, title);
                stopList.add(stop);
            }
            cstopforpoint.close();
        }
        
       if(parser!=null)
       {
        //解析的时候保存一条方向上所有站点的预测信息，通过站点和预测信息之间的对应关系，为相应的站点关联预测信息，关联的条件为tag相同，解析的时候保存有解析数据的tag
        ArrayList<String>pstopTagList=parser.getStopTagList();  
        ArrayList<ArrayList<Prediction>>predictionsList = parser.getPreLists();
        //前提有预测信息被解析到
        if(pstopTagList.size()!=0)
        {
      //  System.out.println("有预测信息的点的个数为"+predictionsList.size());
        for (Stop stop : stopList) {
          for(int i=0,num=pstopTagList.size();i<num;i++)
        	  if(stop.getTag().equals(pstopTagList.get(i)))
			   {
				stop.setPredictions(predictionsList.get(i));
				break;
			   }
		   }
        }
      }
       //将站点信息保存到routeConfig中,其实routeConfig中主要也只有stop的信息被用的，目的是为了便于扩展
        routeConfig.setStops(stopList);
       //操作结束关闭数据库
        db.close();
        return routeConfig;
    }
    
    public void close() {
        dbHelper.close();
    }
    
    public void close(Cursor c, ContentValues values, SQLiteDatabase db) {
        if (c != null) {
            c.close();
            c = null;
        }
        
        if (values != null) {
            values.clear();
            values = null;
        }
        
        if (db != null) {
            db.close();
            db = null;
        }
    }

    /**
     * 初始化或者是清理values
     * 
     * @param values
     */
    public void initValues(ContentValues values) {
        if (values == null) {
            this.values = new ContentValues();
        } else {
            this.values.clear();
        }
    }

    /**
     * 及时释放资源
     * 
     * @param values
     */
    public void releaseValues(ContentValues values) {
        if (values != null) {
            this.values.clear();
            this.values = null;
        }
    }

    // !!!!!!!!!!!!!!!!!!!!!!!!
    /**
     * 查询routes表，得到所有的路线
     */
    /*
     * public Cursor queryAllroute() { Cursor cursor=dbHelper.query(); return
     * cursor; }
     */

    /**
     * 查询某一个表中带有一个where条件
     */
    /*
     * public Cursor queryAllroute(String querySQL,String[] selection) { Cursor
     * cursor=dbHelper.query(querySQL,selection); return cursor; }
     */

}
